 -- EXEC SP_WAFER_PKG_STANDARD_COST_REPORT 2011, 1
 
 ALTER PROC SP_WAFER_PKG_STANDARD_COST_REPORT ( @YEAR INT, @QUARTER INT)
 AS
 BEGIN
  DECLARE @FG_ProcessStep_ID INT
  DECLARE @ASSM_ProcessStep_ID INT
  DECLARE @FG_CURSOR CURSOR 
  DECLARE @BOM_PROCESSSTEP_ID INT
  DECLARE @FAB_ProcessStep_ID INT
  DECLARE @FG_PART_NUMBER VARCHAR(50)
  DECLARE @Package VARCHAR(50)
  DECLARE @WaferCost NUMERIC(18,4)
  DECLARE @BGCost  NUMERIC(18,4)
  DECLARE @PkgCost NUMERIC(18,4)
  DECLARE @testTimeInSec NUMERIC(18,4)
  DECLARE @ROWCOUNT INT
  DECLARE @testCostPerSec NUMERIC(18,4)
  DECLARE @TRStepCost NUMERIC(18,4)
  DECLARE @TRCostY NUMERIC(18,4)
  DECLARE @TRCostT NUMERIC(18,4)
  DECLARE @bulkCost NUMERIC(18,4)
  DECLARE @YieldAssy NUMERIC(18,4)
  DECLARE @YieldTest NUMERIC(18,4)
  DECLARE @LAST_CHAR CHAR
  DECLARE @Yield  NUMERIC(18,4)
  DECLARE @Assembly_Out_Cost NUMERIC(18,4)
  DECLARE @Test_Out_Cost NUMERIC(18,4)
  DECLARE @Mix_Match_Yield NUMERIC(18,4)
  DECLARE @Total_Wafer_Cost NUMERIC(18,4)
  DECLARE @Cumm_Cost_Per_Die NUMERIC(18,4)
  DECLARE @CMOS_Cumm_Cost_Per_Die NUMERIC(18,4)
  DECLARE @MEMS_Cumm_Cost_Per_Die NUMERIC(18,4)
  
  CREATE TABLE #COST_TABLE ( [Part Number] VARCHAR(50), [CMOS Die Cost] NUMERIC(18,4), [MEMS Die Cost] NUMERIC(18,4),
    [ASSEMBLY Step COST] NUMERIC(18,4), [Assembly Yield] NUMERIC(18, 2), [Assembly Out Cost] NUMERIC (18, 4), 
    [Test Step Cost Per Sec] NUMERIC(18,4),[Test Time In Sec] NUMERIC(18,4), [Test Yield] NUMERIC(18,2), [Test Out Cost] NUMERIC (18, 4),
    [TR Step Cost] NUMERIC(18,4), [TR Yield] NUMERIC(18,2), [FG Cost] NUMERIC(18,4), 
    ProcessStep_Id INT, Fab_ProcessStep_ID INT, Assembly_ProcessStep_ID INT, Parent_product_Id INT, Package VARCHAR(50)
    )
 
  INSERT INTO #COST_TABLE( [Part Number], ProcessStep_Id, Parent_product_Id ) SELECT P.Part_Number, P.ProcessStep_ID, P.P_Parent_Product_ID FROM Product P, Process_Step PS
  WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'FG'
 
  SET @FG_CURSOR = CURSOR FOR SELECT ProcessStep_Id, [Part Number] FROM #COST_TABLE
  OPEN @FG_CURSOR
  FETCH NEXT FROM @FG_CURSOR INTO @FG_ProcessStep_ID, @FG_PART_NUMBER
  WHILE @@FETCH_STATUS = 0
  BEGIN
	SELECT @ASSM_ProcessStep_ID = ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @FG_ProcessStep_ID, 0 ) WHERE PS_TYPE = 'Assembly'
	IF( @ASSM_ProcessStep_ID > 0 )
	BEGIN
		SELECT @FAB_ProcessStep_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @ASSM_ProcessStep_ID, 0 ) WHERE PS_TYPE = 'Die Bank'
		SELECT @CMOS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @FAB_ProcessStep_ID AND Quarter = @QUARTER AND Year = @YEAR
 
		SELECT @Package = Parameter_Value FROM Process_Parameter WHERE ProcessStep_Id = @ASSM_ProcessStep_ID AND Parameter_Key = 'Package ID'
 
		SELECT @BOM_PROCESSSTEP_ID = bom_processstep_id FROM Assembly_BOM_Setup_Table WHERE assembly_processstep_id = @ASSM_ProcessStep_ID
		--SELECT @BOM_PROCESSSTEP_ID = ProcessStep_Id FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @BOM_PROCESSSTEP_ID, 0 ) WHERE PS_TYPE = 'FAB'
		SELECT @MEMS_Cumm_Cost_Per_Die = ISNULL( Cumm_Cost_Per_Die, 0.0) FROM Wafer_Pkg_Cost_Table WHERE ProcessStep_ID = @BOM_PROCESSSTEP_ID AND Quarter = @QUARTER AND Year = @YEAR
 
		IF ( (SELECT COUNT(*) FROM [Wafer_Pkg_Cost_Table] WHERE ProcessStep_ID = @FG_ProcessStep_ID AND Quarter = @Quarter AND Year = @Year) > 0 )
		BEGIN
			SELECT @PkgCost = [Pkg Cost], @testTimeInSec = [Test Time InSec], @testCostPerSec = [Test Cost PerSec], @TRCostY = [TR CostY], @TRCostT = [TR CostT],
			@bulkCost = [Bulk], @YieldAssy = [Yield_Assy], @YieldTest = [Yield_Test], @Yield = [Yield]
			FROM [Wafer_Pkg_Cost_Table] WHERE ProcessStep_ID = @FG_ProcessStep_ID AND Quarter = @Quarter AND Year = @Year
		END
		ELSE
		BEGIN
			SELECT @PkgCost = [Pkg Cost], @testTimeInSec = [Test Time InSec], @testCostPerSec = [Test Cost PerSec], @TRCostY = [TR CostY], @TRCostT = [TR CostT],
			@bulkCost = [Bulk], @YieldAssy = [Yield_Assy], @YieldTest = [Yield_Test], @Yield = [Yield]
			FROM [Wafer_Pkg_Cost_Table] WHERE ProcessStep_ID = @ASSM_ProcessStep_ID AND Quarter = @Quarter AND Year = @Year
		END
	    
		SELECT @LAST_CHAR = SubString( @FG_Part_Number , LEN(@FG_Part_Number), 1)
		IF @LAST_CHAR = 'T'
		BEGIN
		 SET @TRStepCost = @TRCostT
		END
		ELSE IF @LAST_CHAR = 'Y'
		BEGIN
		 SET @TRStepCost = @TRCostY
		END
		ELSE
		BEGIN
		 SET @TRStepCost = @bulkCost
		END

	    SELECT @Cumm_Cost_Per_Die = (CASE WHEN @Quarter = 1 THEN Q1_STANDARD_COST
		 WHEN @Quarter = 2 THEN Q2_STANDARD_COST
		WHEN @Quarter = 3 THEN Q3_STANDARD_COST
		ELSE Q4_STANDARD_COST
		END) FROM UPDATE_VIEW_COST_TABLE WHERE ProcessStep_ID = @FG_ProcessStep_ID AND YEAR(FIN_YEAR) = @Year
 
		SET @Assembly_Out_Cost = ( ( ISNULL( @CMOS_Cumm_Cost_Per_Die, 0.0) + ISNULL( @MEMS_Cumm_Cost_Per_Die, 0.0) )/(ISNULL(@YieldAssy, 100.00)/100) ) + ISNULL(@PkgCost, 0.0)
		SET @Test_Out_Cost = ( @Assembly_Out_Cost + ( ISNULL(@testTimeInSec, 1.0)*ISNULL(@testCostPerSec, 0.0) ) )/(ISNULL(@YieldTest, 100.00)/100.00)
    
		UPDATE #COST_TABLE SET [CMOS Die Cost] = @CMOS_Cumm_Cost_Per_Die, [MEMS Die Cost] = @MEMS_Cumm_Cost_Per_Die,
		[ASSEMBLY Step COST] = @PkgCost, [Assembly Yield] = @YieldAssy, 
		[Assembly Out Cost] = @Assembly_Out_Cost,
		[Test Time In Sec]= @testTimeInSec, [Test Step Cost Per Sec] =  @testCostPerSec, [Test Out Cost] = @Test_Out_Cost,
		[Test Yield] =  @YieldTest, [TR Step Cost] = @TRStepCost, [TR Yield] = @Yield, [FG Cost] = @Cumm_Cost_Per_Die, Package = @Package
		WHERE ProcessStep_Id = @FG_ProcessStep_ID
 
   END
   SET @ASSM_ProcessStep_ID = 0
   FETCH NEXT FROM @FG_CURSOR INTO @FG_ProcessStep_ID, @FG_PART_NUMBER
  END 
  CLOSE @FG_CURSOR
  DEALLOCATE @FG_CURSOR
 
  SELECT [Part Number], [CMOS Die Cost], [MEMS Die Cost],[ASSEMBLY Step COST],([Assembly Yield]/100.00) as [Assembly Yield], [Assembly Out Cost], 
    [Test Step Cost Per Sec],[Test Time In Sec], ([Test Yield]/100.00) as [Test Yield], [Test Out Cost],
    [TR Step Cost], ([TR Yield]/100.00) as [TR Yield], [FG Cost], Package
    FROM #COST_TABLE ORDER BY Parent_product_Id, Package
 
 END

